--set hive.mapred.mode ='proceed'
insert overwrite table jms_dm.dm_alarm_cainiao_actual_network_tn_dt
select
    date_time --查询日期
     ,count_t1 --当天时效数量
     ,count_t2 --前1天时效数量
     ,up_diff_count --差异1：前1天有后1天无
     ,current_diff_count --差异2：前1天没有后1天有
     ,aging_diff_cnt --差异3：时效不同
     ,(up_diff_count+current_diff_count+aging_diff_cnt) as diff_count --总差异量
     ,cast((up_diff_count+current_diff_count+aging_diff_cnt)/count_t1 as decimal(16,4)) as diff_rt --差异占比
     ,date_time as dt  --分区
from
(
    select '{{ execution_date  | cst_ds }}' as date_time,count(t1.days) as  count_t1, count(t2.days) count_t2,
           count(case when t2.days is not null and t1.days is null then 1 end) as up_diff_count,
           count(case when t2.days is null and t1.days is not null then 1 end) as current_diff_count,
           count(case when t2.days is not null and t1.days is not null and t1.days<>t2.days then 1 end) as aging_diff_cnt
         --count(case when t2.route_id is not null and t1.route_id is not null and t1.route_id<>t2.route_id then 1 end) as aging_diff_cnt
    from (
             select
                 start_hm,
                 end_hm,
                 taking_code,
                 deliver_code,
                 days,
                 route_id
             from jms_dm.dm_cainiao_active_tn_v2_dt
             where dt='{{ execution_date  | cst_ds }}'
         ) t1
             full join (
        select
            start_hm,
            end_hm,
            taking_code,
            deliver_code,
            days,
            route_id
        from jms_dm.dm_cainiao_active_tn_v2_dt
        where dt=date_add('{{ execution_date  | cst_ds }}',-1)
    ) t2 on t1.start_hm=t2.start_hm  and t1.end_hm=t2.end_hm  and t1.taking_code=t2.taking_code and t1.deliver_code=t2.deliver_code
)tt
distribute by 1;
---------------------------------------------------------
with tab_receipt_adjust as (
    select
        count(distinct(network_code))           as  adjust_all_cnt  --有调整的网点数
         ,count(if(length(is_have_time)=0,1,null)) as  reduce_cnt      --无收件收仓班次的网点数
         ,count(distinct(if(todays_interval_time is not null and yesta_interval_time is not null,network_code,null))) as update_cnt --收件收仓班次的网点数
    from
        (
            select
                ttt.network_code,concat(s1.receipt_start_time,'-',s1.receipt_end_time) as todays_interval_time
                 ,concat(s2.receipt_start_time,'-',s2.receipt_end_time) as yesta_interval_time
                 ,net.provider_desc
                 ,concat_ws('',collect_list(concat(s1.receipt_start_time,'-',s1.receipt_end_time)) over(partition by ttt.network_code)) as is_have_time
            from (
                     select
                         network_code
                     from
                         (
                             select
                                 nvl(aa.network_code,bb.network_code) as network_code,nvl(aa.times,bb.times) as times
                             from
                                 (
                                     select
                                         network_code,times
                                     from
                                         (
                                             select network_code,receipt_start_time as  times
                                             from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt
                                             where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1
                                               and dt ='{{ execution_date | cst_ds }}'
                                             union all
                                             select network_code,receipt_end_time  as times
                                             from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt
                                             where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1
                                               and dt ='{{ execution_date | cst_ds }}'
                                         )a group by network_code,times
                                 )aa full join
                                 (
                                     select
                                         network_code ,times
                                     from
                                         (
                                             select network_code,receipt_start_time   as times
                                             from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt
                                             where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1
                                               and dt =date_add('{{ execution_date | cst_ds }}',-1)
                                             union all
                                             select network_code,receipt_end_time  as times
                                             from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt
                                             where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1
                                               and dt =date_add('{{ execution_date | cst_ds }}',-1)
                                         )b group by network_code ,times
                                 )bb on aa.network_code =bb.network_code  and aa.times =bb.times
                             where aa.network_code is null or bb.network_code is null
                             group by nvl(aa.network_code,bb.network_code) ,nvl(aa.times,bb.times)
                         )tt group by network_code
                 )ttt left join ( --t-1
                select network_code,receipt_start_time  ,receipt_end_time
                from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt
                where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1
                  and dt ='{{ execution_date | cst_ds }}'
            )s1 on ttt.network_code=s1.network_code
                      left join ( --t-2

                select network_code,receipt_start_time  ,receipt_end_time
                from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt
                where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1
                  and dt =date_add('{{ execution_date | cst_ds }}',-1)
            )s2 on ttt.network_code=s2.network_code
                      left join
                 jms_dim.dim_network_whole_massage net on net.code=ttt.network_code
        )tttt

),

     tab_out_shift_adjust as (
         select count(1) as  shift_adjust_cnt --目的网点散货班次调整数量
              ,sum(if(t2.shift is null,1,0)) as  t1_increase_cnt  --增加班次数
              ,sum(if(t1.shift is null,1,0)) as  t1_reduce_cnt  --减少班次数
         from
             (
                 select
                     arrive_network_code,shift
                 from
                     (
                         select
                             depart_network_code depart_network_name,arrive_network_code ,arrive_network_name ,shift ,line_code,line_name,effective_date,expiration_date,dt
                         from jms_dim.dim_tab_tmsnew_time_effective_base_dt
                         where dt ='{{ execution_date | cst_ds }}'
                           and is_enable =1 --and arrive_network_code ='6536170'
                           and date_add('{{ execution_date | cst_ds }}',1) between effective_date and expiration_date
                     )t group by arrive_network_code,shift
             )t1 full join
             (
                 select
                     arrive_network_code,shift
                 from
                     (
                         select
                             depart_network_code depart_network_name,arrive_network_code ,arrive_network_name ,shift ,line_code,line_name,effective_date,expiration_date,dt
                         from jms_dim.dim_tab_tmsnew_time_effective_base_dt
                         where dt =date_add('{{ execution_date | cst_ds }}',-1)
                           and is_enable =1 --and arrive_network_code ='6536170'
                           and '{{ execution_date | cst_ds }}' between effective_date and expiration_date
                     )t group by arrive_network_code,shift
             )t2 on t1.arrive_network_code=t2.arrive_network_code and t1.shift=t2.shift
         where t1.shift is null or t2.shift is null

     )
        ,
     tab_route_id_adjust as (
--route_id 变化
         select
             count(if(nvl(t1.route_id,'')!=nvl(t2.route_id,''),1,null))  as route_id_diff    --线路变化总量
              ,count(if(t1.days!=t2.days,1,null)) as days_diff   --时效不同
              ,count(if(t1.shift is not null and t2.shift is null,1,null)) as shift_diff_t1 --散货班次变化(t-1有t-2没有)
              ,count(if(t1.shift is null and t2.shift is not null,1,null)) as shift_diff_t2 --散货班次变化(t-1没有t-2有)
              ,count(if(t1.shift is not null and t2.shift is not null and nvl(t1.shift,-1)!=nvl(t2.shift,-1),1,null))  as shift_diff_t1_t2  ----散货班次变化(t-1和t-2不同)
              ,count(if( nvl(t1.shift,-1)!=nvl(t2.shift,-1),1,null)) as shift_diff --散货班次变化总量
              ,count(if(t1.inter_time!=t2.inter_time,1,null)) as inter_time_diff   --揽收时段变化总量
              ,count(if(nvl(t1.line,'')!=nvl(t2.line,''),1,null))  as line_diff    --线路变化总量
         from
             (
                 select
                     taking_code
                      , taking_name
                      , deliver_code
                      , deliver_name
                      , start_hm
                      , end_hm
                      , days
                      , date_time
                      , rt
                      , route_id
                      , is_bushu
                      , detail_route_id
                      , tf
                      , if(length(can1)=1 and length(can2)=1,can1,null) as shift
                      , can2
                      , line
                      , inter_time
                      , dt
                 from
                     (
                         select
                             taking_code
                              , taking_name
                              , deliver_code
                              , deliver_name
                              , start_hm
                              , end_hm
                              , days
                              , date_time
                              , rt
                              , route_id
                              , is_bushu
                              , detail_route_id
                              , split(detail_route_id,'-')[0] as  tf
                              , split(detail_route_id,'-')[size(split(detail_route_id,'-'))-1] as can1
                              , split(detail_route_id,'-')[size(split(detail_route_id,'-'))-2] as can2
                              , REGEXP_EXTRACT(detail_route_id, '(-\\d{3,}[A-Z]*\\d+)+',0) as line
                              , concat(split(detail_route_id,'-')[1],'-',split(detail_route_id,'-')[2]) as inter_time
                              , dt
                         from jms_dm.dm_cainiao_active_tn_v2_dt
                         where dt=date_add('{{ execution_date | cst_ds }}',-1)
                     )t
             )t1 inner join
             (
                 select
                     taking_code
                      , taking_name
                      , deliver_code
                      , deliver_name
                      , start_hm
                      , end_hm
                      , days
                      , date_time
                      , rt
                      , route_id
                      , is_bushu
                      , detail_route_id
                      , tf
                      , if(length(can1)=1 and length(can2)=1,can1,null) as shift
                      , line
                      , can2
                      , inter_time
                      , dt
                 from
                     (
                         select
                             taking_code
                              , taking_name
                              , deliver_code
                              , deliver_name
                              , start_hm
                              , end_hm
                              , days
                              , date_time
                              , rt
                              , route_id
                              , is_bushu
                              , detail_route_id
                              , split(detail_route_id,'-')[0] as  tf
                              , split(detail_route_id,'-')[size(split(detail_route_id,'-'))-1] as can1
                              , split(detail_route_id,'-')[size(split(detail_route_id,'-'))-2] as can2
                              , REGEXP_EXTRACT(detail_route_id, '(-\\d{3,}[A-Z]*\\d+)+',0) as line
                              , concat(split(detail_route_id,'-')[1],'-',split(detail_route_id,'-')[2]) as inter_time
                              , dt
                         from jms_dm.dm_cainiao_active_tn_v2_dt
                         where dt='{{ execution_date | cst_ds }}'
                     )t
             )t2 on t1.taking_code=t2.taking_code and t1.deliver_code=t2.deliver_code and t1.start_hm=t2.start_hm

     )

insert overwrite table bidefault.dm_alarm_cainiao_actual_network_tn_dt_add partition(dt='{{ execution_date | cst_ds }}')
select
    sum(route_id_diff) as route_id_diff          --route_id 变化量
     ,sum(days_diff       ) as days_diff        --时效不同
     ,sum(shift_diff_t1   ) as shift_diff_t1    --散货班次变化(t-1有t-2没有)
     ,sum(shift_diff_t2   ) as shift_diff_t2    --散货班次变化(t-1没有t-2有)
     ,sum(shift_diff_t1_t2) as shift_diff_t1_t2 --散货班次变化(t-1和t-2不同)
     ,sum(shift_diff      ) as shift_diff       --散货班次变化总量
     ,sum(inter_time_diff ) as inter_time_diff  --揽收时段变化总量
     ,sum(line_diff       ) as line_diff        --线路变化总量
     ,sum(adjust_all_cnt  ) as adjust_all_cnt   --有调整的网点数
     ,sum(reduce_cnt      ) as reduce_cnt       --无收件收仓班次的网点数
     ,sum(update_cnt      ) as update_cnt       --有变化的收件收仓班次的网点数
     ,sum(shift_adjust_cnt) as shift_adjust_cnt --目的网点散货班次调整数量
     ,sum(t1_increase_cnt ) as t1_increase_cnt  --增加班次数
     ,sum(t1_reduce_cnt   ) as t1_reduce_cnt    --减少班次数
     ,'{{ execution_date | cst_ds }}' as date_time
from
    (
        select
            route_id_diff
             ,days_diff   --时效不同
             ,shift_diff_t1     --散货班次变化(t-1有t-2没有)
             ,shift_diff_t2     --散货班次变化(t-1没有t-2有)
             ,shift_diff_t1_t2  --散货班次变化(t-1和t-2不同)
             ,shift_diff        --散货班次变化总量
             ,inter_time_diff   --揽收时段变化总量
             ,line_diff         --线路变化总量
             ,0 as adjust_all_cnt  --有调整的网点数
             ,0 as reduce_cnt      --无收件收仓班次的网点数
             ,0 as update_cnt      --有变化的收件收仓班次的网点数
             ,0 as shift_adjust_cnt --目的网点散货班次调整数量
             ,0 as t1_increase_cnt  --增加班次数
             ,0 as t1_reduce_cnt  --减少班次数
        from tab_route_id_adjust
        union all
        select
            0 as route_id_diff
             ,0 as days_diff   --时效不同
             ,0 as shift_diff_t1     --散货班次变化(t-1有t-2没有)
             ,0 as shift_diff_t2     --散货班次变化(t-1没有t-2有)
             ,0 as shift_diff_t1_t2  --散货班次变化(t-1和t-2不同)
             ,0 as shift_diff        --散货班次变化总量
             ,0 as inter_time_diff   --揽收时段变化总量
             ,0 as line_diff         --线路变化总量
             ,adjust_all_cnt  --有调整的网点数
             ,reduce_cnt      --无收件收仓班次的网点数
             ,update_cnt      --有变化的收件收仓班次的网点数
             ,0 as shift_adjust_cnt --目的网点散货班次调整数量
             ,0 as t1_increase_cnt  --增加班次数
             ,0 as t1_reduce_cnt  --减少班次数
        from tab_receipt_adjust
        union all
        select
            0 as route_id_diff
             ,0 as days_diff         --时效不同
             ,0 as shift_diff_t1     --散货班次变化(t-1有t-2没有)
             ,0 as shift_diff_t2     --散货班次变化(t-1没有t-2有)
             ,0 as shift_diff_t1_t2  --散货班次变化(t-1和t-2不同)
             ,0 as shift_diff        --散货班次变化总量
             ,0 as inter_time_diff   --揽收时段变化总量
             ,0 as line_diff         --线路变化总量
             ,0 as  adjust_all_cnt  --有调整的网点数
             ,0 as  reduce_cnt      --无收件收仓班次的网点数
             ,0 as  update_cnt      --有变化的收件收仓班次的网点数
             ,shift_adjust_cnt      --目的网点散货班次调整数量
             ,t1_increase_cnt       --增加班次数
             ,t1_reduce_cnt         --减少班次数
        from  tab_out_shift_adjust
    )t
    distribute by 1;